package StudentLesson;

import TeachLesson.CourseWork.CourseWorkEnity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class StuCourseWorkDao {
    //获取数据库连接对象
    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }
    //新增作业
    public int addStuLesson(String courseNo,String workerNo ,int courseId,String filePath,String submitTime,String studentNo,String workDesc){
        Connection connection=this.getCon();
        try {
            String sql="insert into tb_stucoursework (courseNo,workerNo,courseId,filePath,submitTime,studentNo,workDesc) values (?,?,?,?,?,?,?)";
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setString(2,workerNo);
            ps.setInt(3,courseId);
            ps.setString(4,filePath);
            ps.setString(5,submitTime);
            ps.setString(6,studentNo);
            ps.setString(7,workDesc);
            int row=ps.executeUpdate();
            ps.close();
            connection.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }
    //依据教师工号以及课程号查询作业
    public ArrayList<StuCourseWorkEnity> getStuCouseWork(String workerNo,String courseNo,int courseId){
        Connection con=this.getCon();
        try {
            ArrayList<StuCourseWorkEnity> list=new ArrayList<>();
            //sql语句
            String sql="select studentNo,submitTime,id,score from tb_stucoursework where courseNo=? and workerNo=? and courseId=?";
            //ps对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setString(2,workerNo);
            ps.setInt(3,courseId);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                StuCourseWorkEnity stuCourseWorkEnity=new StuCourseWorkEnity();
                stuCourseWorkEnity.setStudentNo(rs.getString("studentNo"));
                stuCourseWorkEnity.setSubmitTime(rs.getString("submitTime"));
                stuCourseWorkEnity.setId(rs.getInt("id"));
                stuCourseWorkEnity.setScore(rs.getString("score"));
                list.add(stuCourseWorkEnity);
            }
            rs.close();
            ps.close();
            con.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }
    //根据id查取文件
    public StuCourseWorkEnity getStuCouWor(int id){
        Connection con=this.getCon();
        try {
            //sql语句
            String sql="select studentNo,submitTime,id,filePath,workDesc,courseNo,workerNo,courseId from tb_stucoursework where id=?";
            //ps对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            StuCourseWorkEnity stuCourseWorkEnity=new StuCourseWorkEnity();
            while (rs.next()){
                stuCourseWorkEnity.setStudentNo(rs.getString("studentNo"));
                stuCourseWorkEnity.setSubmitTime(rs.getString("submitTime"));
                stuCourseWorkEnity.setId(rs.getInt("id"));
                stuCourseWorkEnity.setCourseId(rs.getInt("courseId"));
                stuCourseWorkEnity.setCourseNo(rs.getString("courseNo"));
                stuCourseWorkEnity.setWorkerNo(rs.getString("workerNo"));
                stuCourseWorkEnity.setFilePath(rs.getString("filePath"));
                stuCourseWorkEnity.setWorkDesc(rs.getString("workDesc"));
            }
            rs.close();
            ps.close();
            con.close();
            return stuCourseWorkEnity;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }


    }

    //打分操作
    public int updateScore(int id,String score){
        Connection con=this.getCon();
        try {
            //sql
            String sql="update tb_stucoursework set score=? where id=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,score);
            ps.setInt(2,id);
            //执行sql语句
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    //根据courseId与学号查询分数
    public String getScore(int id,String studentNo){
        Connection con=this.getCon();
        try {
            //sql
            String sql="select score from tb_stucoursework where courseId=? and studentNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setInt(1,id);
            ps.setString(2,studentNo);
            ResultSet rs=ps.executeQuery();
            String score=null;
            while (rs.next()){
                score=rs.getString("score");
            }
            rs.close();
            ps.close();
            con.close();
            return score;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

}
